<!DOCTYPE html>
<html>
  <head>
    <meta charset="UTF-8">
    <title>Synchronizing Fusion Tables with Google Forms</title>
    <link rel="stylesheet" type="text/css" href="../../util/reference_style.css">
    <style type="text/css">
      table.properties, table.properties th, table.properties td {
        border: 1px solid #000;
        border-collapse: none;
        padding: 6px;
      }
      th.property-name {
        width: 110px;
      }
      th.property-value {
        width: 291px;
      }
      th.property-notes {
        width: 473px;
      }
    </style>
  </head>
  <body>
    <h1>
      Synchronizing Fusion Tables with Google Forms
    </h1>
    <p>
      With a simple script, you can set up a Google Form that your users
      can use to input data to a Fusion Table. The script takes advantage
      of <a href="http://code.google.com/googleapps/appsscript/">Google's
           Apps Script tool</a> to transfer new form input into your table.
    </p>
    <p>
      The <a href="reference-roleaccount.html">previous version</a> of these
      instructions required a role account and a publicly visible password;
      this version automatically uses your credentials to safely connect to
      Fusion Tables instead.
    </p>
    <section id="toc">
      <h2>
        Table of Contents
      </h2>
      <p>
        <a href="#begin">Before you begin</a>
      </p>
      Steps
      <ol>              
        <li>
          <a href="#createForm">Create your form, spreadsheet, and Fusion Table</a>
        </li>
        <li>
          <a href="#script">Set up the script</a>
        </li>
        <li>
          <a href="#send">Send the form to your users</a>
        </li>
      </ol>
      <p>
        <a href="#trouble">Troubleshooting</a>
      </p>
    </section>
    <section id="begin">
      <h2>
        Before you begin
      </h2>
      <p>
        Plan out your data. If you are collecting
        <a href="http://code.google.com/apis/fusiontables/docs/developers_guide.html#Geo">geographic
          data</a> that contains a street address such as &quot;1600
        Amphitheatre Parkway, MV, 94043&quot; or a text location string such
        as &quot;Yosemite Park&quot; and you need Fusion Tables to geocode
        them for you, there are some additional steps to follow (noted below).
      </p>
      <p>
        Also note that once you have started collecting data, you cannot
        make any changes to the form or Fusion Table.
      </p>
    </section>
    <section id="createForm">
      <h2>
        Create your form, spreadsheet, and Fusion Table
      </h2>
      <p>
        Your form will be backed by a spreadsheet, to which you will add triggering code
        to copy the responses to a Fusion Table.
      </p>
      <h5>
        Form and spreadsheet
      </h5>
      <ol>
        <li>
          From <a href="https://drive.google.com" target="_blank">Google Drive</a> click
          "Create &gt; Form" to create a new form. Set up your desired form questions
          and give it a useful title.
        </li>
        <li>
          Click "Choose response destination" from the toolbar at the top of the form editor.
          In the resulting dialog select:
          <ol type="a">
            <li>
              Send responses to: Spreadsheet
            </li>
            <li>
              New spreadsheet
            </li>
          </ol>
          Then click "Create".
        </li>
        <li>
          Click on the "View responses" link in the gray bar at the top of the page to see the
          newly created spreadsheet. Note that the form automatically creates a Timestamp column.
        </li>
      </ol>
      <h5>
        Table
      </h5>
      <ol>
        <li>
          From Drive click "Create &gt; Table (Experimental)" to create a new Fusion Table.
          If you don't see that choice:
          <ol type="a">
            <li>
              Click "Connect more apps" at the bottom of the list
            </li>
            <li>
              Type "fusion tables" in the search box followed by the Enter key.
            </li>
            <li>
              Click the blue "+ Connect" button to add the app.
            </li>
            <li>
              You should see a dialog box saying "Google Tables was connected to Google Drive."
              Click the OK button to continue.
            </li>
          </ol>
          <p>
            If you can't find Fusion Tables in the list of apps, try
            <a href="https://support.google.com/fusiontables/answer/2920039">Fix:
              Not in Google Drive menus</a>.
          </p>
        </li>
        <li>
          In the "Import new table" dialog box, select Google Spreadsheets in the left-hand column.
          Select your spreadsheet from the list and import.
        </li>
        <li>
          If you collect location data, choose "Edit &gt; Add column" in the table UI to
          include a column with type "Location" to hold your geocoded coordinates.
        </li>
      </ol>
    </section>
    <section id="script">
      <h2>
        Set up the script
      </h2>
      <h5>
        Create the script project
      </h5>
      <ol>
        <li>
          Click on the browser tab with your form response spreadsheet and click
          "Tools &gt; Script editor..." to bring up the Apps Script project window.
        </li>
        <li>
          If you see a dialog entitled "Google Apps Script", click "Blank Project"
          in the "Create script for" section.
        </li>
      </ol>
      <h5>
        Set up your form's properties
      </h5>
      <ol>
        <li>
          Click "File &gt; Project properties". The first time
          you choose this command, the Rename Project dialog box is
          displayed. After that, the main project properties dialog
          box is displayed.
        </li>
        <li>
          Click the "Project properties" tab.
        </li>
        <li>
          Click "+ Add row" to start adding your custom properties.
          This displays a row with &quot;(name)&quot; and &quot;(value)&quot; placeholders.
          <p>
            Add the docid row, replacing the name and value placeholders:
          </p>
          <table class="properties">
            <thead>
              <tr>
                <th class="property-name">
                  Name
                </th>
                <th class="property-value">
                  Value to use
                </th>
                <th class="property-notes">
                  Notes
                </th>
              </tr>
            </thead>
            <tbody>
              <tr>
                <td>
                  docid
                </td>
                <td>
                  The identifier of your table.
                </td>
                <td>
                  To find this value, select "File &gt; About" in the table menu.
                  Copy the "Id" value.
                </td>
              </tr>
            </tbody>
          </table>
          <p>
            If you collect  location data, also add these properties:
          </p>
          <table class="properties">
            <thead>
              <tr>
                <th class="property-name">
                  Name
                </th>
                <th class="property-value">
                  Value to use
                </th>
                <th class="property-notes">
                  Notes
                </th>
              </tr>
            </thead>
            <tbody>
              <tr>
                <td>
                  addressColumn
                </td>
                <td>
                  The exact name of the column in your table that
                  contains the address. Do not use quotes.
                </td>
                <td>
                  If your data is already geocoded, you do not need
                  this property.
                </td>
              </tr>
              <tr>
                <td>
                  latlngColumn
                </td>
                <td>
                  The exact name of the location column you added to
                  your table.
                </td>
                <td>
                  When the form is submitted, the script geocodes the value in
                  the addressColumn, and saves the lat/lng coordinates in this column.
                </td>
              </tr>
            </tbody>
          </table>
          <p class="warning" style="margin-top: 16px">
            Make sure you have added the location column to your table and have added these
            properties before you run the script. You cannot change your table once you start
            using the script.
          </p>
        </li>
        <li>
          Click "Save" to save the custom properties.
        </li>
      </ol>
      <h5>
        Enable the Fusion Tables service
      </h5>
      <p>
        Follow
        <a href="https://developers.google.com/apps-script/guides/services/advanced#enabling_advanced_services" target="_blank">
          these instructions</a> to enable the use of the Fusion Tables service for
        Apps Script and in the Developer Console.
      </p>
      <h5>
        Add the code
      </h5>
      <ol>
        <li>
          Your new script has some placeholder code. Replace it with
          <a href="../src/formsync.js" target="_blank">this code</a>.
        </li>
        <li>
          Click "File &gt; Save".
        </li>
      </ol>
      <h5>
        Authorize the script
      </h5>
      <ol>
        <li>
          Click "Run &gt; checkAuthorization" from the top menu.
          You will see an "Authorization Required" dialog box.
        </li>
        <li>
          Click the "Continue" button. This brings up another window
          spelling out what your access the script needs. Click the
          blue "Accept" button to authorize the script; this adds a
          token to your project properties so you won't have to do this step
          again. See this
          <a href="https://developers.google.com/apps-script/guides/services/authorization"
             target="_blank">help article</a> for more information on authorization,
          including how to revoke access.
        </li>
      </ol>
      <h5>
        Add the triggers
      </h5>
      <p>
        Spreadsheet triggers run Apps Script code based on various events. Here you add triggers
        to run the "onFormSubmit" method when the form is submitted with a new answer,
        and optionally to run a general synchronization periodically.
      </p>
      <ol>
        <li>
          Click "Resources &gt; Current project's triggers".
        </li>
        <li>
          Choose these values from the dropdown menus:
          <b>Run</b>: onFormSubmit, <b>Events:</b> From spreadsheet, On form submit
        </li>          
        <li>
          Click "File &gt; Save".
        </li>
        <li>
          Optional: You can set up a trigger to run the <code>sync</code>
          method periodically.
          <ol type="a">
            <li>
              Click "Resources &gt; Current script's triggers", then "Add a new trigger".
            </li>
            <li>
              Choose these values from the dropdown menus:
              <b>Run</b>: sync, <b>Events:</b> Time-driven, Hour timer, Every hour.
            </li>
          </ol>
        </li>
        <li>
          Click "Save".
        </li>
      </ol>
      <h5>
        Set up error notifications
      </h5>
      <p>
        Triggers can notify you of problems by email. When you are first getting set up,
        it's a good idea to set up notifcations so you can see what's going on.
        This <a href="https://developers.google.com/apps-script/understanding_triggers"
                target="_blank">help page</a>
        has more details at the bottom of the page in the "Errors in Triggers" section.
      </p>
      <ol>
        <li>
          Click "Resources &gt; Current project's triggers".
        </li>
        <li>
          Click on the "Notifications" link for the "onFormSubmit" or "sync" trigger.
        </li>
        <li>
          Choose the appropriate time to email. When you are first getting set up, choosing
          "immediately" will make it easier to see what's going wrong.
        </li>          
        <li>
          Click "Save".
        </li>
      </ol>
      <h5>
        Try it all out
      </h5>
      <ol>
        <li>
          Go to your live form: from the form editor click "View live form" in the gray bar at the
          top of the page.
        </li>
        <li>
          Enter some form values and click "Submit".
        </li>
        <li>
          After a few seconds, check that the spreadsheet has the new values, and also
          has a new column called "rowid".
        </li>
        <li>
          Check that the Fusion Table also has the new values. You will have to refresh the
          web page to get the latest data.
        </li>
        <li>
          If either of these is not working, from the script editor click
          "View &gt; Execution transcript" and "View &gt; Logs" to see if anything shows up.
          You might also get an error notification email.
        </li>
        <li>
          You're done! Now you need some real data.
        </li>
      </ol>
    </section>

    <section id="send">
      <h2>
        Send the form to your users
      </h2>
      <p>
        From the Google Form, click the blue "Send Form" button and choose one of
        these sharing options:
      </p>
      <ul>
        <li>
          Send form via email: enter your users' email addresses
        </li>
        <li>
          Send a link: click the URL under the "Link to share" label, copy it, and send
        </li>
        <li>
          Embed the form: Click the "Embed" button and follow the instructions
        </li>
      </ul>
      <p>
        Once people start submitting form entries and the synchronization has run, you will see
        the "rowid" column in your spreadsheet filling up with the unique numbers used by
        Fusion Tables to identify rows. These are used by the script to apply updates and
        deletions correctly.
      </p>
      <p class="warning">
        Don't change the rowids &mdash; that could cause the synchronization to
        have unexpected results.
      </p>
    </section>
    <section id="trouble">
      <h2>
        Troubleshooting &amp; FAQs
      </h2>
      
      <h3>
        My users entered  incorrect location data.
      </h3>
      <ol>
        <li>
          No worries! Open the spreadsheet and fix the data.
        </li>
        <li>
          Click "Tools &gt; Script editor..." to go to the Script Project window.
        </li>
        <li>
          Click "Run &gt; sync" from the top menu. This updates your table with the
          corrected spreadsheet data.
        </li>
      </ol>
      <h3>
        My data is not being saved to Fusion Tables.
      </h3>
      <ul>
        <li>
          Double check that the column names in the spreadsheet and Fusion
          Table match exactly. By default, they should match, but if for any
          reason they do not, go in and change the spreadsheet columns to
          match the table's columns.
        </li>
        <li>
          Make sure that you saved your properties in the Project Properties tab,
          <i>not</i> the User Properties tab.
        </li>
      </ul>
      <h3>
        I want to add another question to my form now that the form is up
        and running and collecting data.
      </h3>
      <ul>
        <li>
          Unfortunately adding another question isn't currently supported. You can't modify the table's
          columns once the script has been used.
        </li>
      </ul>
      <h3>
        "ReferenceError: "FusionTables" is not defined."
      </h3>
      <ul>
        <li>
          Ensure that you <a href="https://developers.google.com/apps-script/guides/services/advanced#enabling_advanced_services" target="_blank">enabled the Fusion Tables Service</a>.
        </li>
      </ul>
      <h3>
        Something else is wrong.
      </h3>
      <ul>
        <li>
          From the script editor check the script logs by selecting "View &gt; Logs" and
          "View > Execution transcript". Review the errors to see what line of code the
          issue is occurring on and submit a bug report in the
          <a href="https://code.google.com/p/fusion-tables-api-samples/issues/list">issue tracker</a>.
        </li>
      </ul>
    </section>
  </body>
</html>
